//Summary Stats


clear all
use  "[Folder]\Panel_Firm_Labv1.dta"

drop EU_*

drop _merge

gen Cap =  Ftg_Anlaggningstillgangar
gen logCap =  ln(Cap)
gen logGrosInvTot=ln(Ftg_Summa_bruttoinv)
gen logFtg_Foradlingsvarde =ln(Ftg_Foradlingsvarde)

//Drop likely error values that cannot be included in regressions
drop if Ftg_Nettoomsattning<0 
drop if Ftg_Anlaggningstillgangar<0 
drop if Ftg_Foradlingsvarde<0

drop if Org_SektorKod<20 //Drop public sector
drop u

//Generate firm categories
egen u = rowmiss( logFtg_Foradlingsvarde logCap logGrosInvTot)
egen u2= rowmiss( logFtg_Foradlingsvarde logCap )
gen u3=u
recode u (0=1) if FirmSize<6
gen u4=u2
recode u2 (0=1) if FirmSize<6
drop if u4!=0 & (Org_SektorKod==23 | Org_SektorKod==24 | Org_SektorKod==25 )
drop if u4!=0 & Org_SektorKod>22 
gen CollapseCategories_0=1
replace CollapseCategories_0=0 if u4==0

gen CollapseCategories_1=1
replace CollapseCategories_1=0 if u4!=0

gen CollapseCategories_2=1
replace CollapseCategories_2=0 if u2!=0

gen CollapseCategories_3=1
replace CollapseCategories_3=0 if u!=0

egen TotCount_Firms=count(CollapseCategories_0)

//Change units to millions
replace Ftg_Anlaggningstillgangar=Ftg_Anlaggningstillgangar/1000     
replace Ftg_Foradlingsvarde=Ftg_Foradlingsvarde/1000
replace Ftg_Eget_kapital=Ftg_Eget_kapital/1000
replace Ftg_Summa_nettoinv=Ftg_Summa_nettoinv/1000
replace Ftg_Summa_bruttoinv=Ftg_Summa_bruttoinv/1000
replace Ftg_Nettoomsattning=Ftg_Nettoomsattning/1000
replace Ftg_Omsattningstillgangar=Ftg_Omsattningstillgangar/1000


gen TotAssets= Ftg_Omsattningstillgangar + Ftg_Anlaggningstillgangar 

//Generate totals and averages
foreach x in Ftg_Nettoomsattning Ftg_Foradlingsvarde Ftg_Omsattningstillgangar Ftg_Anlaggningstillgangar TotAssets Ftg_Summa_bruttoinv FirmSize  {
egen Tot`x'_0=total(`x')
egen Av`x'_0=mean(`x')
}


gen Count_Firms_0 =TotCount_Firms
egen Count_Firms_1 =total(CollapseCategories_1)
egen Count_Firms_2 =total(CollapseCategories_2)
egen Count_Firms_3 =total(CollapseCategories_3)
sum Count_Firms_*



forvalues i=1/3 {
foreach x in Ftg_Nettoomsattning Ftg_Foradlingsvarde  Ftg_Omsattningstillgangar Ftg_Anlaggningstillgangar TotAssets Ftg_Summa_bruttoinv FirmSize  {
egen Tot`x'_`i'=total(`x') if CollapseCategories_`i'==1
egen Av`x'_`i'=mean(`x')  if CollapseCategories_`i'==1

}
}

cd "[Output Folder]\"
//Put values in excel table
putexcel set "\\Mfso03\MyDocs\ekandr\My Documents\T1_FirmSumStat_v1", replace

putexcel A2 ="Av. Employment"
putexcel A3 ="Tot. Employment"
putexcel A4 ="Av. Sales"
putexcel A5 ="Tot. Sales"

putexcel A6 ="Av. Fixed Assets"
putexcel A7 ="Tot. Fixed Assets"

putexcel A8 ="Av. Tot. Assets"
putexcel A9 ="Tot. Tot. Assets"

putexcel A10 ="Av. Investment"
putexcel A11 ="Tot. Investment"



putexcel A12 ="Av. Value Added"
putexcel A13 ="Tot. Value Added"

putexcel A14 ="Number of Firms"


putexcel B1 ="All Firms"
putexcel C1 ="Firms With Positive VA and Fixed Assets"
putexcel D1 ="Firms in Sample"
putexcel E1 ="Firms in OP Sample"

local i = 2
disp `i'
foreach x in AvFirmSize TotFirmSize AvFtg_Nettoomsattning TotFtg_Nettoomsattning  AvFtg_Anlaggningstillgangar TotFtg_Anlaggningstillgangar AvTotAssets TotTotAssets AvFtg_Summa_bruttoinv TotFtg_Summa_bruttoinv AvFtg_Foradlingsvarde TotFtg_Foradlingsvarde Count_Firms {
	
	
	sum `x'_0
	putexcel B`i'=`r(mean)', nformat(number_sep) //div by 7 for av. over the 7 years	
	sum `x'_1
	putexcel C`i'=`r(mean)', nformat(number_sep) //div by 7 for av. over the 7 years	
	
	sum `x'_2
	putexcel D`i'=`r(mean)', nformat(number_sep) //div by 7 for av. over the 7 years	
	sum `x'_3
	putexcel E`i'=`r(mean)', nformat(number_sep) //div by 7 for av. over the 7 years	
	 local ++i
	disp `i'
}


